/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.twistinjava.category.modelDAO;
import com.twistinjava.category.model.Category;
import com.twistinjava.exception.TwistinjavaException;
import com.twistinjava.logon.User;
import com.twistinjava.util.DBManager;
import com.twistinjava.util.LoggableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.logging.*;
/**
 *
 * @author shikhar
 */
public class CategoryDAO
{

    private static Log log = LogFactory.getLog(CategoryDAO.class);

    public void saveCategory(Category category) throws TwistinjavaException, SQLException
    {
        log.debug("inside saveCategory(Category category) of CategoryDAO class ********");
        Connection conn = null;
        StringBuilder sqlQry = new StringBuilder();
        PreparedStatement pStmt = null;
        try
        {
            conn = DBManager.getConnection();
            sqlQry.append("insert into Category (categoryName)").append("values (?)");
            pStmt = new LoggableStatement(conn, sqlQry.toString());
            pStmt.setString(1, category.getCategoryName());
            pStmt.execute();
        }
        catch (SQLException sQLException)
        {
            log.error("Exception inside saveCategory(Category category) method of CategoryDAO class ********" + sQLException.getMessage());
            throw new TwistinjavaException(sQLException.getMessage());
        }
        finally
        {
            try
            {
                if (pStmt != null)
                {
                    pStmt.close();
                }
                if(conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException sqle)
            {
                log.error("Inside CategoryDAO :saveCategory(Category category) method of CategoryDAO class :" +sqle.getMessage());
            }

        }
    }

    public ArrayList getAllCategory() throws TwistinjavaException
    {
        log.debug("Inside getAllCategory() method of CategoryDAO Class ****************");
        String sqlQry = "";
        Connection conn = null;
        PreparedStatement pStmt = null;
        ArrayList categories = new ArrayList();
        try
        {
            conn = DBManager.getConnection();
            sqlQry = "select id, categoryName from Category";
            pStmt = new LoggableStatement(conn, sqlQry.toString());
            ResultSet rs = pStmt.executeQuery();
            while (rs.next())
            {
               Category category = new Category();
                category.setId(rs.getInt("id"));
                category.setCategoryName(rs.getString("categoryName"));
                categories.add(category);

            }
        }
        catch (SQLException sQLException)
        {
            log.error("Exception inside getAllCategory() method of CategoryDAO class ********" + sQLException.getMessage());
            throw new TwistinjavaException(sQLException.getMessage());
        }
        finally
        {
            try
            {
                if (pStmt != null)
                {
                    pStmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException sqle)
            {
                log.error("Inside CategoryDAO :getAllCategory() method of CategoryDAO class :" + sqle.getMessage());
            }

        }
        return categories;
    }
    public void deleteCategory(int id) throws TwistinjavaException
    {
        log.debug("Inside deleteCategory(int id) method of CategoryDAO Class ****************");
        String sqlQry = "";
        Connection conn = null;
        PreparedStatement pStmt = null;
        try
        {
            conn = DBManager.getConnection();
            sqlQry = "delete from Category where id = ?";
            pStmt = new LoggableStatement(conn, sqlQry.toString());
            pStmt.setInt(1, id);
            pStmt.execute();

        }
        catch (SQLException sQLException)
        {
            log.error("Exception inside deleteCategory(int id) method of CategoryDAO class ********" + sQLException.getMessage());
            throw new TwistinjavaException(sQLException.getMessage());
        }
        finally
        {
            try
            {
                if (pStmt != null)
                {
                    pStmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException sqle)
            {
                log.error("Inside CategoryDAO :deleteCategory(int id) method of CategoryDAO class :" + sqle.getMessage());
            }

        }

    }

        public Category getCategory(int id) throws TwistinjavaException
    {
        log.debug("Inside getCategory(int id) method of CategoryDAO Class ****************");
        String sqlQry = "";
        Connection conn = null;
        PreparedStatement pStmt = null;
        Category category = null;
        try
        {
            conn = DBManager.getConnection();
            sqlQry = "select id, categoryName from Category where id = ?";
            pStmt = new LoggableStatement(conn, sqlQry.toString());
            pStmt.setInt(1, id);
            ResultSet rs = pStmt.executeQuery();
            if (rs.next())
            {
                category = new Category();
                category.setId(rs.getInt("id"));
                category.setCategoryName(rs.getString("categoryName"));

            }
        }
        catch (SQLException sQLException)
        {
            log.error("Exception inside getCategory(int id) method of CategoryDAO class ********" + sQLException.getMessage());
            throw new TwistinjavaException(sQLException.getMessage());
        }
        finally
        {
            try
            {
                if (pStmt != null)
                {
                    pStmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException sqle)
            {
                log.error("Inside CategoryDAO :getCategory(int id) method of CategoryDAO class :" + sqle.getMessage());
            }

        }
        return category;
    }
    public void updateCategory(Category category) throws TwistinjavaException
    {
        log.debug("updateCategory(Category category) method of CategoryDAO Class ****************");
        String sqlQry = "";
        Connection conn = null;
        PreparedStatement pStmt = null;

        try
        {
            conn = DBManager.getConnection();
            sqlQry = "Update Category set categoryName = ? where id = ?";
            pStmt = new LoggableStatement(conn, sqlQry.toString());
            pStmt.setString(1, category.getCategoryName());
            pStmt.setInt(2, category.getId());

            pStmt.executeUpdate();

        }
        catch (SQLException sQLException)
        {
            log.error("Exception insideupdateCategory(Category category) method of CategoryDAO class ********" + sQLException.getMessage());
            throw new TwistinjavaException(sQLException.getMessage());
        }
        finally
        {
            try
            {
                if (pStmt != null)
                {
                    pStmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException sqle)
            {
                log.error("Inside CategoryDAO :updateCategory(Category category) method of CategoryDAO class :" + sqle.getMessage());
            }

        }

    }

}
